from openpyxl import load_workbook
import os

'''
读取Excel数据写入到word文档中
'''

path = os.getcwd() + os.sep
print(path)
workbook = load_workbook(path + 'data.xlsx')
sheet = workbook.active
print(sheet.dimensions)

# SQE
SQE = sheet['Q2'].value
#供应商&制造商
supplier = sheet['G2'].value
#采购单号
c2_10 = sheet['C2:C10']
vC2_10 = [str(cell[0].value) for cell in c2_10]
order_num = ','.join(set(vC2_10))
order_num_title = '&'.join(set(vC2_10))
#产品型号
T2_10 = sheet['T2:T10']
vT2_10 = [str(cell[0].value) for cell in T2_10]
ptype = ','.join(set(vT2_10))
#产品描述
P2_10= sheet['P2:P10']
vP2_10 = [str(cell[0].value) for cell in P2_10]
info = ','.join(set(vP2_10))
info_title = '&'.join(set(vP2_10))
#日期
import datetime
today = datetime.date.today()
time = today.strftime('%Y%m%d')
#验货数量
V2_10 = sheet['V2:V10']
vV2_10 = [int(cell[0].value) for cell in V2_10]
total_num = sum(vV2_10)
#验货箱数
W2_10= sheet['W2:W10']
vW2_10 = [int(cell[0].value) for cell in W2_10]
box_num = sum(vW2_10)
#生成文件名
title = f'{order_num_title}-{supplier}-{total_num}-{info_title}-{time}.docx'
#print(title)

from docx import Document
doc = Document(path + 'temp.docx')
tables = doc.tables
print(len(tables))
tables[0].cell(1,1).text = SQE
tables[1].cell(1,1).text = supplier
tables[1].cell(2,1).text = supplier
tables[1].cell(3,1).text = ptype
tables[1].cell(4,1).text = info
tables[1].cell(5,1).text = order_num
tables[1].cell(7,1).text = time

for i in range(2,11):
    tables[6].cell(i,0).text = str(sheet['T{}'.format(i)].value)
    tables[6].cell(i,1).text = str(sheet['P{}'.format(i)].value)
    tables[6].cell(i,2).text = str(sheet['C{}'.format(i)].value)
    tables[6].cell(i,4).text = str(sheet['V{}'.format(i)].value)
    tables[6].cell(i,5).text = str(sheet['V{}'.format(i)].value)
    tables[6].cell(i,6).text = '0'
    tables[6].cell(i,7).text = str(sheet['W{}'.format(i)].value)
    tables[6].cell(i,8).text = '0'

tables[6].cell(12,4).text = str(total_num)
tables[6].cell(12,5).text = str(total_num)
tables[6].cell(12,7).text = str(box_num)

doc.save(path + '{}.docx'.format(title))

